package com.huayue.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.Room;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 房间基本信息
 */
@Component
public class RoomDao implements BaseDao<Room> {
    @Override
    public int insert(Room entity) {
        String roomSql = "INSERT INTO room (name, room_type_id, size, toword, is_deleted ) VALUES (?, ?, ? , ? ,?);";
        DBHelper.executeUpdate(roomSql, entity.getName(), entity.getRoom_type_id(), entity.getSize(),
            entity.getToword(), 0);
        String statusSql = "insert into room_status(room_id,status_id) values((select max(id) from room),1)";
        return DBHelper.executeUpdate(statusSql);
    }

    @Override
    public int update(Room entity) {
        return DBHelper.executeUpdate("update room set name=?,room_type_id=?,size=?,toword=? where id=?",
            entity.getName(), entity.getRoom_type_id(), entity.getSize(), entity.getToword(), entity.getId());
    }

    @Override
    public int delete(Room entity) {
        return DBHelper.executeUpdate("update room set is_deleted=1 where id=?", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        String sql = "select r.id id, r.room_type_id room_type_id, r.name room_name,rt.name type_name,"
            + "r.size room_size,r.toword room_toword,r.is_deleted room_is_deleted  from room r"
            + " INNER JOIN room_type rt on r.room_type_id=rt.id where r.is_deleted=0";
        return DBHelper.executeQuery(sql);
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        Room room = (Room)obj[0];
        String sql =
            "select r.id id,  r.room_type_id room_type_id,r.name rname,rt.name typename,r.size rsize,r.toword rtoword,r.is_deleted ris_deleted  from room r "
                + "INNER JOIN room_type rt on r.room_type_id=rt.id where 1=1 and r.is_deleted=0 ";
        if (obj[0] != null && !"".equals(room.getName())) {
            sql += " and r.name like '%" + room.getName() + "%'";
        }
        return DBHelper.executeQuery(sql + " order by r.id desc limit ?,? ", page.getBegin(), page.getPageSize());

    }

    @Override
    public Map<String, Object> findOne(Room entity) {
        return DBHelper.executeQuery("select * from room  where is_deleted=0 and id=? ", entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        String sql =
            "select count(*) counts from room r INNER JOIN room_type rt on r.room_type_id=rt.id where 1=1 and r.is_deleted=0  ";
        // 传进来一个Object对象,将其强转为所需对象
        Room room = (Room)obj[0];
        if (obj[0] != null && !"".equals(room.getName())) {
            sql += " and r.name like '%" + room.getName() + "%'";
        }

        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());

    }

    public List<Map<String, Object>> findRoom_type(Object... obj) {
        return DBHelper.executeQuery("select * from room_type where is_deleted=0");
    }

    /**
     * 查询room表所有内容
     * 
     * @return
     */
    public List<Map<String, Object>> find() {
        return DBHelper.executeQuery("select id,name room_name from room where is_deleted = 0");
    }

    /**
     * 查询没有入住和预定的房间
     * 
     * @param obj
     * @return
     */
    public List<Map<String, Object>> findRoom(Object... obj) {
        String sql =
            "select r.id id, r.room_type_id room_type_id, r.name room_name,rt.name type_name,r.size room_size,r.toword room_toword,r.is_deleted room_is_deleted  from room r \n"
                + " INNER JOIN room_type rt on r.room_type_id=rt.id \n"
                + " INNER JOIN room_status rs on rs.room_id=r.id \n"
                + " INNER JOIN room_status_type rst on rs.status_id=rst.id "
                + " where r.is_deleted=0 and (rs.status_id=1 or rs.status_id=4 or rs.status_id=5) order by rs.gmt_modified desc";
        return DBHelper.executeQuery(sql);
    }

    public List<Map<String, Object>> findAllRoomAndType() {
        String sql = "select rt.id rtid,rt.name rtname,r.name,r.id,rs.status_id,rt.price from room_type rt\n"
            + " left join room r on rt.id = r.room_type_id left join room_status rs on rs.room_id=r.id\n"
            + " where r.is_deleted=0";
        return DBHelper.executeQuery(sql);
    }

    /**
     * 根据房间名称查询总收入
     */
    public List<Map<String, Object>> findTotalPriceByRoom(PageHelper page, Room room, String begin, String end) {
        String sql = "select rt.id,r.name,rt.name tname,rt.floors,rt.size,IFNULL(d.tprice,0) price from room r\n"
            + "left join room_type rt on r.room_type_id=rt.id\n"
            + "left join  (select ro.id,sum(rrod.price) tprice from room ro\n"
            + "left join room_real_order_details rrod on rrod.room_id=ro.id where rrod.days != 0 ";
        if (begin != null && end != null) {
            sql += " and rrod.gmt_modified between '" + begin + "' and '" + end + "' ";
        }
        sql += " group by ro.id)d on d.id= r.id" + " where concat(rt.name,r.name) like '%" + room.getName()
            + "%' order by price desc limit ?,? ";

        return DBHelper.executeQuery(sql, page.getBegin(), page.getPageSize());
    }

    /**
     * 根据房间名称查询总收入总条数
     */
    public int findTotalPriceByRoomCount(Room room, String begin, String end) {
        String sql = "select count(*) counts from room r left join room_type rt on r.room_type_id=rt.id\n"
            + "left join  (select ro.id,sum(rrod.price) tprice from room ro\n"
            + "left join room_real_order_details rrod on rrod.room_id=ro.id where rrod.days != 0 ";
        if (begin != null && end != null) {
            sql += " and rrod.gmt_modified between '" + begin + "' and '" + end + "' ";
        }
        sql += " group by ro.id)d on d.id= r.id  where concat(rt.name,r.name) like '%" + room.getName() + "%' ";
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }
}
